Creating New Columns And New Measures 8

This step is the most important step because the story of the dashboard begins with this step so please make sure to follow all the steps exactly as it shown below.

New Column #1

1/Go to Edit Query/Transform 

2/Duplicate Energy column 



3/Rename the Duplicated column to exactly  Legend Of Energy Type 

4/Replace Values of the power plant name with 1 and 2.

1 means Renewable 

2 means Nonrenewable 

Replace 

Natural Gas = 2

Coal= 2

Nuclear=2

Hydroelectric Conventional=1

Wind = 1

Solar Thermal and Photovoltaic=1

Geothermal = 1

*Do the same steps for all power plants.

5/ Change type to Text



New Column #2

Same Process As New Column#1

1/ Duplicate Energy column 

2/Rename the Duplicated column to exactly  Types of Energy 2

3/Replace 

Natural Gas = 3

Coal= 2

Nuclear=4

Hydroelectric Conventional=1

Wind = 1

Solar Thermal and Photovoltaic=1

Geothermal = 1

4/Change Type to Whole Number 



New Column #3

In this column we will create a  date calendar

Merge 3 column 

1/Date 

2/Month Name

3/Year 

  

After 

2/Rename the column to Full Date 

3/Change type to Date/Time


4/ Finally Click "Close and apply"


NEW COLUMN #4

MAKE YEAR COLUMN 



Filter Generation 

Filter out all negative value from the generation column.

Mini value must be 0





New Measure 

Run All this DAX below 

1/

2/Run all the DAX one by one.



Dax Formulas

1/.0YOYRenewableKPI = IFERROR( ([.0Renewable total Cy]-[.0LyRenewable])/[.0LyRenewable],BLANK())

 

2/.0Renewable total Cy = Var Mdd=Max(Main_PowerSource[Year]) return CALCULATE([.Renewable   TotalPower], Main_PowerSource[Year]=Mdd)

 

3/.0LyRenewable = (CALCULATE([.Renewable TotalPower], PARALLELPERIOD(Main_PowerSource[Full    Date],-1,YEAR))/[.0Yearscount])

 

4/.0Yearscount = If (DISTINCTCOUNT(Main_PowerSource[Year])-1=0,1,DISTINCTCOUNT(Main_PowerSource[Year])-1)

 

5/.Renewable TotalPower = CALCULATE(sum(Main_PowerSource[Generation]),Main_PowerSource[Types of Energy 2]=1)

 

6/.0unichar=SWITCH(true(),[.0YOYRenewableKPI]>=[.02LyYOYRenewableKPI],UNICHAR(9650),[.0YOYRene    wableKPI]<[.02LyYOYRenewableKPI],UNICHAR(9660))

 

7/.02LyYOYRenewableKPI=IFERROR(CALCULATE([.0YOYRenewableKPI],PARALLELPERIOD(Main_PowerSource[Full Date],-1,YEAR)),BLANK())

 

8/.YOY Renewable M = If ([.0YOYRenewableKPI]>=0,[.0YOYRenewableKPI],(([.2YOY Nonrenewable]*-1)+[.0YOYRenewableKPI]))

 

9/.2YOY Nonrenewable = IFERROR( ([.2Nonrenewable total Cy]-[.2LyNonRenewable])/[.2LyNonRenewable],BLANK())

 

10/.NonRenewable TotalPower=CALCULATE(sum(Main_PowerSource[Generation]),Main_PowerSource[Types of Energy 2]>1)

 

 

11/.2Nonrenewable total Cy = Var Mdd=Max(Main_PowerSource[Year]) return CALCULATE([.NonRenewable TotalPower], Main_PowerSource[Year]=Mdd)


12/..Generation% = DIVIDE([.1Total Generation Cy],CALCULATE([.1Total Generation Cy],ALLSELECTED(Main_PowerSource)))

 

13/.YOY Power Gen = IFERROR(([.1Total Generation Cy]-[.1Total Generation Ly])/[.1Total Generation Ly],blank())

 

14/.1Total Generation Ly = (CALCULATE(sum(Main_PowerSource[Generation]), PARALLELPERIOD(Main_PowerSource[Full Date],-1,YEAR))/[.0Yearscount])


15/.kpirenewablecolor = SWITCH(TRUE(),[.0YOYRenewableKPI] >=0,1,[.0YOYRenewableKPI]<0,2)



 

16/.Barcolor = Switch(true(),[.NonRenewable TotalPower]>=[.2LstNonrenewable],"#4ca973","#DE6A73")



17/.GBarChartColor = (SWITCH(True(),[.NonRenewable TotalPower]=sum(Main_PowerSource[Generation]),"#808080","#cd9f61"))



 

18/.0unichar=SWITCH(true(),[.0YOYRenewableKPI]>=[.02LyYOYRenewableKPI],UNICHAR(9650),[.0YOYRenewableKPI]<[.02LyYOYRenewableKPI],UNICHAR(9660))


 

19/.0unichorcolor = Switch(true(),[.0YOYRenewableKPI]>=0,"#22BC22","#ff0000")


20/.2LyNonRenewable = (CALCULATE([.NonRenewable TotalPower], PARALLELPERIOD(Main_PowerSource[Full Date],-1,YEAR))/[.0Yearscount])


21/.2LstNonrenewable = Var Yar=Min(Main_PowerSource[Year])-1 RETURN (CALCULATE([.NonRenewable TotalPower],Main_PowerSource[Year]=Yar))


22/.1Total Generation Cy = Var Mdd=Max(Main_PowerSource[Year]) return CALCULATE(sum(Main_PowerSource[Generation]), Main_PowerSource[Year]=Mdd)


8:36 AM



https://www.youtube.com/watch?v=g5yZIk2rYGc
https://www.youtube.com/watch?v=mp86Hya3L2M
https://www.youtube.com/watch?v=SVEGfqCTodc